ORA-01552 non-system tablespace [message #641] |
Mon, 25 February 2002 10:19 |
Clayton Stordahl
Messages: 3 Registered: February 2002
|
Junior Member |
|
|
I had this running in Oracle 8i Enterprise, but when I move up to Oracle 9i Enterprise, I get an ORA-01552: cannot use system rollback segment for non-system tablespace 'BIGROLL'
Here is the sql part and the Table BIGROLL exist's:
CREATE PUBLIC ROLLBACK SEGMENT BIGROLL
TABLESPACE BIGROLL
STORAGE(INITIAL 10M
NEXT 10M
MINEXTENTS 10
MAXEXTENTS 50
OPTIMAL NULL)
/
ALTER ROLLBACK SEGMENT BIGROLL ONLINE
/
I am logged in a sysdba also.
Could it be a permissions problem?
Please help, and thanks in advance.
-Clayton
|
|
|
Re: ORA-01552 non-system tablespace [message #657 is a reply to message #641] |
Tue, 26 February 2002 02:00 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
when u get this error ? how many rollback seg u have in system and non-system
have u changed the rollback_segment parameter in init.ora
ORA-01552 cannot use system rollback segment for non-system tablespace "
name"
Cause: An attempt was made to use the SYSTEM rollback segment for operations involving a non-system tablespace.
Action: Create one or more rollback segments and then use ALTER ROLLBACK SEGMENT 'name' online. It may be necessary to modify the initialization parameter ROLLBACK_SEGMENTS to acquire one of the new private rollback segments. Before creating a rollback segment outside the SYSTEM tablespace, it is necessary to first create and activate a non-system rollback segment in the SYSTEM tablespace.
|
|
|
Re: ORA-01552 non-system tablespace [message #672 is a reply to message #657] |
Tue, 26 February 2002 05:05 |
Clayton Stordahl
Messages: 3 Registered: February 2002
|
Junior Member |
|
|
I only have one rollback segment and that is for the system tablespace.
I did change the init.ora because when I first ran the script it gave me the error:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
-Clayton
|
|
|
Re: ORA-01552 non-system tablespace [message #674 is a reply to message #641] |
Tue, 26 February 2002 05:34 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
Before creating a rollback segment outside the SYSTEM tablespace, it is necessary to first create and activate a non-system rollback segment in the SYSTEM tablespace.
so connect as system and create anoter rollback segment in system tablespace and make it online, then create other rollback segment
|
|
|
Re: ORA-01552 non-system tablespace [message #2220 is a reply to message #641] |
Fri, 28 June 2002 12:58 |
kwalther
Messages: 9 Registered: April 2002
|
Junior Member |
|
|
Don't forget that when you create your own rollback
segments, to have to bring them online after creating
them. Issue this command for a rollback segment named
rbs01..
alter rollback segment rbs01 online;
Pretty stupid, but thats what you have to do.
|
|
|
|
Re: ORA-01552 non-system tablespace [message #2331 is a reply to message #672] |
Mon, 08 July 2002 04:09 |
Manish Bharucha
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
Oracle9i databases are capable of managing their own undo (rollback) segments. No longer will administrators need to carefully plan and tune the number and sizes of rollback segments or decide how to strategically assign transactions to a particular rollback segment. Oracle9i also allows administrators to allocate their undo space in a single undo tablespace with the database taking care of issues such as undo block contention, consistent read retention, and space utilization.
*************check this with your DB expert ----->
UNDO_SUPPRESS_ERRORS
Parameter type Boolean
Default value false
Parameter class Dynamic: ALTER SESSION, ALTER SYSTEM
Range of values true | false
UNDO_SUPPRESS_ERRORS enables users to suppress errors while executing manual undo management mode operations (for example, ALTER ROLLBACK SEGMENT ONLINE) in automatic undo management mode. Setting this parameter enables users to use the undo tablespace feature before all application programs and scripts are converted to automatic undo management mode. For example, if you have a tool that uses SET TRANSACTION USE ROLLBACK SEGMENT statement, you can add the statement "ALTER SESSION SET UNDO_SUPPRESS_ERRORS = true" to the tool to suppress the ORA-30019 error.
If you want to run in automatic undo management mode, ensure that your tools or applications are updated to run in automatic undo management mode.
We trust this resolution fixes
|
|
|